-- @owner: songjing20
-- @date: 2024-7-12
-- @testpoint: connect by子句,prior关键字后+类型转换

--step1:创建普通表，插入数据;expect:建表成功，插入数据成功
drop table if exists t_connectby_0023 cascade;
create table t_connectby_0023(name varchar, id int, fatherid int);
insert into t_connectby_0023 values('001', 1, 0),('002', 2, 1),('003', 3, 1);
insert into t_connectby_0023 values('004', 4, 5),('005', 5, 2),('006', 6, 2);
insert into t_connectby_0023 values('007', 1, 6),('008', 1, 2),('009', 1, 2);

--step2:prior关键字后+类型转换，查询id=1作为初始集合的递归查询;expect:成功
select * from t_connectby_0023 start with id=1 connect by nocycle prior name::int >= fatherid limit 10;

--step3:清理环境;expect:成功
drop table t_connectby_0023 cascade;
